<?php

namespace App\Http\Controllers;

//use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
use Illuminate\Http\UploadedFile;
use App\Util\SqlServerUtil;
use App\Util\Common;
//use Excel;
use PHPExcel;
use App;
use Illuminate\Support\Facades\DB; 
use Illuminate\Database\Query\Builder;
// use App\libs\phpExcel\Classes\PHPExcel;
use Excel;
use Symfony\Component\HttpFoundation\BinaryFileResponse;

use App\Http\Controllers\Api\CommonController;
use phpDocumentor\Reflection\DocBlock\Tags\Var_;
use Illuminate\Support\Facades\Mail;



class ExcelController extends Controller
{
	
	protected $cs_1_13 = 1.13;
	protected $cs_1_06 = 1.06;
		
	 public function __construct(Request $request){
	 	$temp = SqlServerUtil::getModelDbs($request);
        if($temp){
            $this->SqlServer = $temp['SqlServer'];
        }
    }
    
public function read($filename,$encode='utf-8')  
{
       include_once(app_path().'/libs/phpExcel/Classes/PHPExcel/IOFactory.php');   
      //$this->load ->library('PHPExcel/IOFactory');  
  
        $objReader =\PHPExcel_IOFactory::createReader('Excel5');  

        $objReader->setReadDataOnly(true);  
  
        $objPHPExcel= $objReader->load($filename);  
  
        $objWorksheet= $objPHPExcel->getActiveSheet();  
  
        $highestRow =$objWorksheet->getHighestRow();  
  
        $highestColumn = $objWorksheet->getHighestColumn();  
  
        //echo$highestColumn;die;  
  
		$highestColumnIndex =\PHPExcel_Cell::columnIndexFromString($highestColumn);
		$excelData =array();
  
        for($row = 2;$row <= $highestRow; $row++){
  
            for ($col= 0; $col < $highestColumnIndex; $col++){
  
            	$excelData[$row-2][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getFormattedValue();  
             }  
  
        }  
        return$excelData;  
	} 
	
	public function _readExcel($path){
		$objPHPExcel = new \PHPExcel();
		$type = 'Excel5';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
		$ext = explode('.',$path);
		
		if($ext[1]=='xlsx'){
			$type = 'Excel2007';
		}
		$xlsReader = \PHPExcel_IOFactory::createReader($type);
		$xlsReader->setReadDataOnly(true);
		$xlsReader->setLoadSheetsOnly(true);
		$Sheets = $xlsReader->load($path,$encode='utf-8');
  		$objWorksheet = $Sheets->getActiveSheet();
  		
		$highestRow =$objWorksheet->getHighestRow();// 取得总行数
		$highestColumn = $objWorksheet->getHighestColumn();// 取得总列数
		
		$highestColumnIndex =\PHPExcel_Cell::columnIndexFromString($highestColumn);
		$excelData =array();
		for($row = 2;$row <= $highestRow; $row++){
			for ($col= 0; $col < $highestColumnIndex; $col++){
				$excelData[$row-1][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getFormattedValue();
			}
		}
		return$excelData;
	} 
	
	
	public function download(request $request){
		$pathToFile = $request->get('d');
		return response()->download($pathToFile);
	}
	 
	
	/**
	* 聚一科技 ^_^
	* 基本费用   
	* @date: 2021年1月12日
	* @author: lq
	* @version:App V2.0
	*/
	public function basefyImport(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$file = $request->file('file');
		//临时绝对路径
		$realPath = $file->getRealPath();
		//获取 后缀名getClientOriginalExtension
		$ext = $file->getClientOriginalExtension();
		//获取原始文件名getClientOriginalName
		$filename =$file->getClientOriginalName();
		$fileurl = Common::Http().$_SERVER['HTTP_HOST'].'/excel/'.$filename;
		
		$url = $_SERVER['HTTP_HOST'].'/uploads/'.$filename;
		$name= 'jbfy.xls';
		$res = Storage::disk('excels')->put($name,file_get_contents($realPath));
		//excels
		if($res){
			$filePath = public_path().'/excel/'.$name;
			$objPHPExcel = new PHPExcel();
			$re =$this->read($filePath,3,'utf-8');
			$fields  = ['fyear','fperiod','fnumber','fname','fjbgz','fzwdj',
					'fycq','fyxj','fscq','fpyj','fpj','flyj',
					'flj','fmjew','fglgz','fhsbt','fgwbt','fyxbt',
					'fcj','fdhfbt','ftcbt','fjlbt','fhj','fnj',
					'fsbkk','flyk','fkgy','fkjk','ffpk','fksdf','fkqnote'
			];
			
			if($re && count($re)>=1){
				//判断是否与模板一致
				if(count($re[0]) !== count($fields)) {
					return response()->json(Common::errorResponse(700))	;
				}
				// 				dd($re ,count($fields)  );
				$data =[];
				foreach ($re  as $k  =>$v )
				{
					foreach ($v as $k1 =>$v1 )
					{
						$data[$k][$fields[$k1]]  = $v1 ;
					}
				}
				$title_no =join("','", array_column($data, 'FNumberkq'));  
				
				$str ="'".$title_no ."'";
				$sql ="select fnumber,fitemid from t_emp where 1=1 and FNumber in ({$str})";  
				$itmeNumber = SqlServerUtil::exec($this->SqlServer,$sql);
// 				dd($data , $itmeNumber);
				//临时数据
				$tempData=[];
				if($data && $itmeNumber){
					foreach($data as $k2 =>&$v2) {
						$v2['fitemid']=0;
						foreach($itmeNumber as $k =>$v) {
							if($v2['fnumberkq'] == $v['fnumber']) {
								$v2['fitemid'] = $v['fitemid'];
							}
						}
					}
					$zhfyData=[];
					$Fitemids = join("','", array_column($data, 'fitemid'));
// 					dd($Fitemids );
					
					$sql ="select fnumberkq,fitemid,fid from gz_gzzykq where 1=1 and FItemID in ('{$Fitemids}') and FYear='{$data[$k2]['FYear']}' and FPeriod='{$data[$k2]['FPeriod']}'";
					$zhfyData = SqlServerUtil::exec($this->SqlServer,$sql);
					$insert1=false;
					$update=false;
					$insert2 =false;
					$arr=[];
// 					dd($zhfyData);
					if (sqlsrv_begin_transaction($this->SqlServer) === false) {
						die(print_r(sqlsrv_errors(), true));
					}
					if($zhfyData && count($zhfyData)>0){
						foreach ($data as $ls =>&$lsVal){
							$lsVal['fid']=0;
							foreach ($zhfyData as $itemkey=>$itemVal){
								if($lsVal['fnumberkq'] == $itemVal['fnumberkq'])
								{
									$lsVal['fid'] = $itemVal['fid'];
									//存在
									$arr[$ls] =$lsVal;
									unset($data[$ls]);
								}
							}
						}
						$insert1=true;
						$update=true;
// 						$insert2  =true;
// 						dd($arr ,$data );
						if($data && count($data)>0){
							foreach( $data as $k5=>$v5) {
								unset($data[$k5]['fid']);
							}
							
							if(count($data)>400){
								$each=400;
								$total =count($data); //数据总数
								$step=ceil($total/$each); //insert执行总次数
								$j = 1;
								$s = $step;
								foreach ($data as $k=>$v)
								{
									$arr0=[];$Tmp0 =[];
									if($j > $step) break;
									$arr0 = array_slice($data, ($step - $s) * $each, $each);     // 每次取500条
									$insert1 =SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_gzzykq',$arr0);
									$j++;
									$s--;
								}
									
							}else{
								$insert1 =SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_gzzykq',$data);
							}
							
// 							$insert1 =SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_gzzykq',$data);
						}
						if($arr && count($arr)>0){
							foreach ($arr as $k3=>$v3)
							{
								unset($arr[$k3]['fyear']);
								unset($arr[$k3]['fperiod']);
								unset($arr[$k3]['fitemid']);
								unset($arr[$k3]['fnumberkq']);
								unset($arr[$k3]['fnamekq']);
							}
							if(count($arr)>200){
								$each=200;
								$total =count($arr); //数据总数
								$step=ceil($total/$each); //insert执行总次数
								$j = 1;
								$s = $step;
								foreach ($arr as $k=>$v)
								{
									$item1=[];$emp1 =[];
									if($j > $step) break;
									$emp1= array_slice($arr, ($step - $s) * $each, $each);     // 每次取500条
									//dd($emp1,$item1);
									$update=SqlServerUtil::sqlsrvkqUpdateNOFID($this->SqlServer,'gz_gzzykq',$emp1);
									$j++;
									$s--;
								}
							}else{
								$update=SqlServerUtil::sqlsrvkqUpdateTosql($this->SqlServer,'gz_gzzykq',$arr);
							}
							
							
						}
					}else{
						//没有全部添加
// 						dd($data);
						if(count($data)>400){
							$each=400;
							$total =count($data); //数据总数
							$step=ceil($total/$each); //insert执行总次数
							$j = 1;
							$s = $step;
							foreach ($data as $k=>$v)
							{
								$arr2=[];$Tmp2 =[];
								if($j > $step) break;
								$arr2 = array_slice($data, ($step - $s) * $each, $each);     // 每次取500条
								//$str = "'".$title_no ."'";
								$insert2 = SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_gzzykq', $arr2);
								$j++;
								$s--;
							}
						}else{
							$insert2 =SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_gzzykq',$data);
						}
					}
// 					dd(($insert1 && $update) , $insert2 , $update);
					if(($insert1 && $update) || $insert2 || $update){
						sqlsrv_commit($this->SqlServer);
						return response()->json(Common::successResponse(200,'上传成功'))	;
					}else{
						sqlsrv_rollback($this->SqlServer);
						return response()->json(Common::errorResponse(400,'上传文件失败1'))	;
					}
				}else{
					return response()->json(Common::errorResponse(400,'基本费用上传失败,职员基本费用表没有导入该员工'))	;
					//showMsg(400,'基本费用上传失败,职员基本费用表没有导入该员工');
				}
			}else{
				return response()->json(Common::errorResponse(400,'上传文件失败2'))	;
			}
		}else{
			return response()->json(Common::errorResponse(400,'上传文件失败3'));
		}
		
	}
	
	
	/**
	 * 聚一科技 ^_^
	 * 计件
	 * @date: 2021年1月12日
	 * @author: lq
	 * @version:App V2.0
	 */
	public function jjImport(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$file = $request->file('file');
		$FRepKey = $request->get('FRepKey');
		//临时绝对路径
		$realPath = $file->getRealPath();
		//获取 后缀名getClientOriginalExtension
		$ext = $file->getClientOriginalExtension();
		//获取原始文件名getClientOriginalName
		$filename =$file->getClientOriginalName();
		$fileurl = Common::Http().$_SERVER['HTTP_HOST'].'/excel/'.$filename;
		$zhiyuan ='jj';
		if($FRepKey =='12225'){
			$zhiyuan ='jj';
		}elseif($FRepKey =='12013'){
			$zhiyuan ='js';
		}
		$name= $zhiyuan.'.xlsx'; 
		
		$url = $_SERVER['HTTP_HOST'].'/uploads/'.$filename;
		$res = Storage::disk('excels')->put($name,file_get_contents($realPath));
		
		//excels
		if($res){
			$filePath = public_path().'/excel/'.$name;
			$objPHPExcel = new PHPExcel();
			$re =$this->_readExcel($filePath );
			$data =[];
			if($re && count($re)>0){
				//判断是否与模板一致
				$re = array_values($re);
				$FRepFldData = SqlServerUtil::execTosql($this->SqlServer,"select frepfld,frepcap,frepmeg from gz_RepListTitle where FRepKey ='{$FRepKey}' and FRepHide =0 and FUnControl =0  order by FRepIndex");
				$tempfname = [];
				$tempfield = [];
				if($FRepKey =='12225'){
					foreach ($FRepFldData as $k =>$v){
						if(strtolower($v['frepfld']) == 'fentrytext2'){
							$FRepFld[$k]= $v;
							$FRepFld[$k+1]['frepfld']= 'fempcode';
							$FRepFld[$k+1]['frepcap']= '工号';
							$FRepFld[$k+1]['frepmeg']= 0 ;
						}else{
							$FRepFld[] = $v;
							
						}
					}
				}elseif($FRepKey =='12013'){
					foreach ($FRepFldData as $k =>$v){
						if(strtolower($v['frepfld']) == 'fname'){
							$FRepFld[$k]= $v;
							$FRepFld[$k+1]['frepfld']= 'fnumber';
							$FRepFld[$k+1]['frepcap']= '工号';
							$FRepFld[$k+1]['frepmeg']= 0 ;
						}else{
							$FRepFld[] = $v;
						}
					}
				}
				
				foreach ($FRepFld as $k =>$v)
				{
					$tempfield[$k] = $v;
					$tempfield[$k]['frepfld'] = strtolower($v['frepfld']) ;
					$tempfname[] = $v['frepcap'];
				} 
				
				$result =[];
				foreach ($re as $k =>$v)
				{
					foreach ($v as $k1 =>$v1)
					{
						 
						$result[$k][$tempfield[$k1]['frepfld']] = $v1;
					}
				}
				
				$db = columnBiao($FRepKey);
				
				if($FRepKey =='12225'){
					$title_no =join("','", array_column($result, 'fempcode'));
					
				}elseif ($FRepKey =='12013'){
					$title_no =join("','", array_column($result, 'fnumber'));
				}
			
				$str ="'".$title_no ."'";
				$sql ="select fnumber,fitemid from gz_emp where 1=1 and FNumber in ({$str})";
				$itmenumber = SqlServerUtil::exec($this->SqlServer,$sql);
				
				//临时数据
				$tempData=[];
				$insert2 = true;
				//
				if($result && count($result)>0){
					if (sqlsrv_begin_transaction($this->SqlServer) === false) {
						die(print_r(sqlsrv_errors(), true));
					}
					if($FRepKey =='12225'){
						foreach ($result as $k3=>$v3)
						{
							$result[$k3]['fplanamount'] = $v3['fplanprice'] * $v3['fqtylp']  ;
						}
					}elseif ($FRepKey =='12013'){
						foreach($result as $k2 =>&$v2) {
							$v2['fitemid']=0;
							foreach($itmenumber as $k =>$v) {
								if($v2['fnumber'] == $v['fnumber']) {
									$v2['fitemid'] = intval($v['fitemid']) ;
									unset($v2['fname']);
									unset($v2['fnumber']);
								}
							}
							if(!empty($v2['ftsgz']) && $v2['ftsgz']>0){
								$v2['fjsamount']=$v2['ftsgz'];
								$v2['fgongshi'] = 0;
								$v2['fjsgz'] = 0;
							}else{
								if(empty($v2['fgongshi'])){
									$v2['fgongshi'] = 1;
								}
								$v2['fjsamount']= $v2['fgongshi'] * $v2['fjsgz'] ;
							}
						}
					}
// 					dd($result);
					$insert2 =SqlServerUtil::sqlsrvAllTosql($this->SqlServer,$db,$result);
					if(  $insert2 ){
						sqlsrv_commit($this->SqlServer);
						showMsg(200,'上传成功');
					}else{
						sqlsrv_rollback($this->SqlServer);
						showMsg(400,'上传文件失败1');
					}
					
				}else{
					showMsg(400,'计件工资上传失败4');
				}
			}else{
				showMsg(400,'上传文件失败2');
			}
		}else{
			showMsg(400,'上传文件失败3');
		}
	}
	
	public function getprice($gxcode){
			$fplanprice =0;
			$wl = SqlServerUtil::exec($this->SqlServer,"select fplanprice,fname,fnumber,FApproveNo from t_ICItem where fnumber ='{$gxcode}'",2);
			if(!empty($wl)){
				$fplanprice = $wl['fplanprice'];
			}
			return $fplanprice;
	}
	
	
	//js---计时
	public function jsImport(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$file = $request->file('file');
		//临时绝对路径
		$realPath = $file->getRealPath();
		//获取 后缀名getClientOriginalExtension
		$ext = $file->getClientOriginalExtension();
		//获取原始文件名getClientOriginalName
		$filename =$file->getClientOriginalName();
		$fileurl = Common::Http().$_SERVER['HTTP_HOST'].'/excel/'.$filename;
		$name= 'js.xls';
		$url = $_SERVER['HTTP_HOST'].'/uploads/'.$filename;
		$res = Storage::disk('excels')->put($name,file_get_contents($realPath));
		
		//excels
		if($res){
			$filePath = public_path().'/excel/'.$name;
			$objPHPExcel = new PHPExcel();
			$re =$this->read($filePath,3,'utf-8');
			$data =[];
			if($re && count($re)>0){
				//判断是否与模板一致
				$re = array_values($re);
				if(count($re[0]) !==8){
					return response()->json(Common::errorResponse(700))	;
				}
				//修改和插入
				for($i=1;$i<count($re);$i++){
					$data[]=[
							'FYear'=>$re[$i][0], //年度
							'FPeriod'=>$re[$i][1],  //期间
							'FNumber'=>$re[$i][2], //工号
							//'FName'=>$re[$i][3], //姓名
							'FBatchNo'=>$re[$i][4], //时间批号
							'FJSGZ'=>!empty($re[$i][5])?$re[$i][5]:0, //计时工资
							'FGongShi'=>!empty($re[$i][6])?$re[$i][6]:0,//工时
							'FTSGZ'=>!empty($re[$i][7])?$re[$i][7]:0,//按天工资
							
							'FjsAmount'=> getFjsAmount($re[$i][5] ,$re[$i][6],$re[$i][7] ),//合计金额
							
							
					];
				}
				//数组去重
				$data123 = assoc_unique($data,'FNumber');
				$title_no =join("','", array_column($data123, 'FNumber'));
				
				$str ="'".$title_no ."'";
				$sql ="select FNumber,FItemID from t_emp where 1=1 and FNumber in ({$str})";
				$itmeNumber = SqlServerUtil::exec($this->SqlServer,$sql);
				
				//临时数据
				$tempData=[];
				$insert2 = true;
				if($data && count($data)>0){
					 foreach($data as $k2 =>&$v2) {
						$v2['FItemID']=0;
						foreach($itmeNumber as $k =>$v) {
							if($v2['FNumber'] == $v['FNumber']) {
								$v2['FItemID'] = intval($v['FItemID']) ;
							}
						}
					}
					if (sqlsrv_begin_transaction($this->SqlServer) === false) {
						die(print_r(sqlsrv_errors(), true));
					}
					foreach ($data as $k3=>$v3)
					{
						unset($data[$k3]['FNumber']);
						unset($data[$k3]['FName']);
					}
					$insert2 =SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_gzzyjs',$data);
					if(  $insert2 ){
						sqlsrv_commit($this->SqlServer);
						return response()->json(Common::successResponse(200,'上传成功'))	;
					}else{
						sqlsrv_rollback($this->SqlServer);
						return response()->json(Common::errorResponse(400,'上传文件失败1'))	;
					}
					
				}else{
					showMsg(400,'计时工资上传失败');
				}
			}else{
				return response()->json(Common::errorResponse(400,'上传文件失败2'))	;
			}
		}else{
			return response()->json(Common::errorResponse(400,'上传文件失败3'));
		}
	}
	
	
	
	/**
	* 文件用途描述
	* @date: 2021年3月11日 下午2:30:34
	* @author: juyi
	* 组导入
	*/
	public function groupImport(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$file = $request->file('file');
		//dd($request->all() );
		//临时绝对路径
		$realPath = $file->getRealPath();
		//获取 后缀名getClientOriginalExtension
		$ext = $file->getClientOriginalExtension();
		//获取原始文件名getClientOriginalName
		$filename =$file->getClientOriginalName();
		$fileurl = Common::Http().$_SERVER['HTTP_HOST'].'/excel/'.$filename;
		$name= 'group.xls';
		$url = $_SERVER['HTTP_HOST'].'/uploads/'.$filename;
		$res = Storage::disk('excels')->put($name,file_get_contents($realPath));
		//dd($file,$realPath,$res);
		//excels
		if($res){
			$filePath = public_path().'/excel/'.$name;
			$objPHPExcel = new PHPExcel();
			$re =$this->read($filePath,3,'utf-8');
			$data =[];$Temp=[];
// 			dd($re );
			$fields  = ['fparentname','fnumber','fname'];
			if($re && count($re)>1){
				//判断是否与模板一致
				if(count($re[0]) !== count($fields)) {
					return response()->json(Common::errorResponse(700))	;
				}
				unset($re[0]);
				$re = array_values($re);
				$result =[];
				foreach ($re  as $k  =>$v )
				{
					foreach ($v as $k1 =>$v1 )
					{
						$result[$k][$fields[$k1]]  = $v1 ;
					}
				}
// 				dd($result  );
				//修改和插入
				$insert21 = false;
				$update2 = false;
				$arr =[];
				$arr1=[];
	
				if (sqlsrv_begin_transaction($this->SqlServer) === false) {
					die(print_r(sqlsrv_errors(), true));
				}
		       $insert21 = SqlServerUtil::sqlsrvAllTosql($this->SqlServer,'gz_xcxgroup', $result);
				///////11111
				if($insert21){
					sqlsrv_commit($this->SqlServer);
					return response()->json(Common::successResponse(200,'上传成功'))	;
				}else{
					sqlsrv_rollback($this->SqlServer);
					return response()->json(Common::errorResponse(400,'上传文件失败'))	;
				}
			
		  }else{
		   	return response()->json(Common::errorResponse(400,'数据为空，上传文件失败'));
		  }
			
		}else{
			return response()->json(Common::errorResponse(400,'上传文件失败3'));
			}
    
	}
	
	/**
	* 基础资料获取
	* @date: 2022年10月26日 下午6:11:29
	* @author: juyi
	* @param: variable
	* @return:
	*/
	public function getjichuziliao(Request $request){
		$typeid = $request->get('typeid');
		if($typeid == 30){
			//部门名称 30
			$sql_1= "SELECT finterid,fname,ftypeid,fid FROM gz_submessage where FTypeID= 30 order by ftypeid ,fid ";
		}elseif ($typeid ==  23){
			//学历 23
			$sql_1="select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 23 order by ftypeid ,fid";
		}elseif ($typeid == 29){
			//职务 29
			$sql_1="select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 29 order by ftypeid ,fid";
		}elseif ($typeid == 102){
			//性别 102
			$sql_1="select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 102 order by ftypeid ,fid";
		}elseif ($typeid == 26){
			//区域 26
			$sql_1="select finterid,fname from gz_submessage where FTypeID= 26 order by ftypeid ,fid";
		}elseif ($typeid == 200){
			//离职 200
			$sql_1="select finterid,fname from gz_submessage where FTypeID= 200 order by ftypeid ,fid";
		}elseif ($typeid == 301){
			//单位  301
			$sql_1="select finterid,fname from gz_submessage where FTypeID= 301 order by ftypeid ,fid";
		}elseif ($typeid == 302){
			//公司名称  302
			$sql_1="select finterid,fname from gz_submessage where FTypeID= 302 order by ftypeid ,fid";
		}else{
			$sql_1="SELECT finterid ,fname,ftypeid,fid FROM gz_submessage where FTypeID= 30
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 23
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 29
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 102
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 26
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 301
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 302
			UNION all
			select finterid,fname,ftypeid,fid from gz_submessage where FTypeID= 200 order by ftypeid ,fid ";
			
			$Data = SqlServerUtil::exec($this->SqlServer,$sql_1);
			$temp=['23'=>'fdegree','29'=>'fduty','30'=>'fdepid','102'=>'fgender','26'=>'quyu','200'=>'fdeleted','301'=>'funit','302'=>'fcompany'];
			$Data2 =[];
			foreach ($Data as $k =>$v)
			{
				$Data2[$temp[$v['ftypeid']]][] =$v;
				//$Data2['bb'][$temp[$v['ftypeid']]][] =$v;
			}
			
			return $Data2;
		}
		
		$Data = SqlServerUtil::exec($this->SqlServer,$sql_1);
		return $Data;
	}
	
	/**
	*  工资条
	* @date: 2022年10月26日 下午6:12:01
	* @author: juyi
	*/
	public function gongziTiaoxz(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$FRepKey = $request ->FRepKey ;
		
		$keyword = $request ->keyword ;
		//年度
		$qjValue = $request ->qjValue ;
		//条数
		$limit = $request ->limit ;
		$page = $request ->page??1 ;
		
		$headerKeys = $request ->headerKeys  ; //字段
		$header = $request ->header  ; //标题
// 		dd($request ->all());
		$year = getCurrentTime(5);
		$mon = getCurrentTime(7);
		$keywordStr = '';
		$timeStr ='';
		if ($request->has('keyword') and $request->keyword != '' and !empty($request->keyword)) {
			$keyword =  $request->keyword ;
			$keywordStr=" and (t1.FNumber like '%{$keyword}%' or t1.FName like '%{$keyword}%'  )";
		}
		
		if($request->has('qjValue') && $request->qjValue != '' && !empty($request->qjValue) && $request->qjValue != 'null'){
			list($year,$mon) = explode('-',$request->qjValue);
			$timeStr .=" and v1.FYear = '{$year}'";
			$mon = intval($mon);
			$timeStr .=" and v1.FPeriod = '{$mon}'";
		}
		
		$sqlData = getfileSql($FRepKey);
		if(!$sqlData){
			showMsg(1,'设置Sql不存在');
		}
		$sql = $sqlData;
		$vowels = array("\n", "\r", "\t");
		$sql = str_replace($vowels, ' ',$sql);
		
		$sql = str_replace('{$keyword}', $keywordStr, $sql);
		$sql = str_replace('{$timeStr}', $timeStr, $sql);
		$sql = str_replace('{$Year}', $year, $sql);
		$sql = str_replace('{$Month}', $mon, $sql);
		
		$sql = str_replace('{$fcompany}', '', $sql);
		$sql = str_replace('{$fstatus}', '', $sql);
		$sql = str_replace('{$fdeleted}', '', $sql);
		$sql=" select top $limit * from ( $sql ) temp_row where rownumber>(($page-1)*$limit)";
		
		$sql = iconv( 'UTF-8','GBK',$sql);
		$res = SqlServerUtil::exec1($this->SqlServer,$sql);
		if(empty($res)){
			$res =[];
		}
		$dataTemp =[];
		$data=[];
		if($res && $headerKeys){
			$res = arraykeyToLower($res);
			
			foreach ($res as $k =>$v)
			{
				foreach ($headerKeys as $k1=>$v1)
				{
					$dataTemp[$k][$v1] = $v[$v1]==='.00'?0:$v[$v1];
				}
			}
			foreach($dataTemp as $k2=>$v2){
				$data[] =  $header ;
				$data[] = array_values($v2);
			}
		}
		// 		dd($data);
		
		$title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q','R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
				'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',
				'BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK'];
		array_unshift($data,['工资发放月份 ：'.$qjValue]);
		$len = count($headerKeys);
		$filename = 'GZT'.$qjValue.'-'.date('Y-m-d-His',time());
		
		$width = [];
		for($i=0;$i<$len;$i++){
			$width[$title_array[$i]] =8;
		}
		
		$this->excelImg($data,$filename,$len,$width,$title_array);
		showMsg(0,'成功',storage_path('exports/'.$filename).'.xlsx');
	}
	
	
	function excelImg($cellData,$filename,$len,$width,$title_array){
		// 		dd($cellData,$filename,$len,$width);
		$length = $title_array[$len-1];
		
		$fw ='A1:'.$length.'1';
		$fw5='A2:'.$length.'2';
		Excel::create($filename,function ($excel) use ($cellData,$fw, $fw5,$title_array,$width,$length){
			$excel->sheet('score',function ($sheet) use ($cellData,$fw, $fw5,$title_array,$width,$length) {
				//第一行合并单元格
				$sheet->mergeCells($fw);
				$sheet->setWidth($width);
				$sheet->getStyle('A:'.$length)->getAlignment()->setWrapText(TRUE);
				$sheet->cells($fw,function($cells) {
					$cells->setAlignment('center');
					$cells->setFontFamily('宋体');
					$cells->setFontWeight('bold');
					$cells->setFontSize(36);
				});
					
					$sheet->cells('A:'.$length, function($cells) {
						$cells->setAlignment('center');
						$cells->setValignment('center');
					});
						
						for($i=0;$i<count($cellData);$i++){
							if($i %2 ==1 && $i !=0 ){
								$sheet->cell('A'.($i+1).':'.$length.($i+1), function ($cell) use ($cellData) {
									$cell->setBackground('#eee');
								});
							}
						}
						//遍历数据
						for($i=0;$i<sizeof($cellData);$i++){
							foreach($cellData[$i] as $k=>$v){
								//否则放置文字数据
								$sheet->cell($title_array[$k] . ($i+1), function ($cell) use ($v) {
									$cell->setValue(' '.$v);
									$cell->setBorder('thin', 'thin', 'thin', 'thin');
									
								});
							}
							
						}//写好结束
			});
		})->store('xlsx');
	}
	
	
	/**
	*  发送
	* @date: 2022年10月26日 下午6:12:26
	* @author: juyi
	*/
	public function gztSingle(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$FRepKey = $request ->FRepKey ;
		
		$keyword = $request ->keyword ;
		//年度
		$qjValue = $request ->qjValue ;
		//条数
		$limit = $request ->limit ;
		$page = $request ->page??1 ;
		
		$headerKeys = $request ->headerKeys  ; //字段
		$header = $request ->header  ; //标题
		$filename = $request ->filename  ; //标题+
		
		$year = getCurrentTime(5);
		$mon = getCurrentTime(7);
		$keywordStr = '';
		$timeStr ='';
		if ($request->has('keyword') and $request->keyword != '' and !empty($request->keyword)) {
			$keyword =  $request->keyword ;
			$keywordStr=" and (t1.FNumber like '%{$keyword}%' or t1.FName like '%{$keyword}%'  )";
		}
		
		if($request->has('qjValue') && $request->qjValue != '' && !empty($request->qjValue) && $request->qjValue != 'null'){
			list($year,$mon) = explode('-',$request->qjValue);
			$timeStr .=" and v1.FYear = '{$year}'";
			$mon = intval($mon);
			$timeStr .=" and v1.FPeriod = '{$mon}'";
		}
		
		$sqlData = getfileSql($FRepKey);
		if(!$sqlData){
			showMsg(1,'设置Sql不存在');
		}
		$sql = $sqlData;
		$vowels = array("\n", "\r", "\t");
		$sql = str_replace($vowels, ' ',$sql);
		
		$sql = str_replace('{$keyword}', $keywordStr, $sql);
		$sql = str_replace('{$timeStr}', $timeStr, $sql);
		$sql = str_replace('{$Year}', $year, $sql);
		$sql = str_replace('{$Month}', $mon, $sql);
		
		$sql = str_replace('{$fcompany}', '', $sql);
		$sql = str_replace('{$fstatus}', '', $sql);
		$sql = str_replace('{$fdeleted}', '', $sql);
		$sql=" select top $limit * from ( $sql ) temp_row where rownumber>(($page-1)*$limit)";

		$res = SqlServerUtil::exec1($this->SqlServer,$sql);
		if(empty($res)){
			$res =[];
		}
		$dataTemp =[];
		$data=[];
		if($res && count($res)>0){
			$res = arraykeyToLowerTo($res);
			foreach ($res as $k =>&$v)
			{
				
				foreach ($headerKeys as $k1=>$v1)
				{
					if( $v[$v1] ==='.00' ){
						$v[$v1] = floatval($v[$v1]);
					}
				}
			}
			$dataTemp = $res ;
			foreach ($dataTemp as $k2=>$v2){
				$name = $v2['fname'] ;
				$subject = $qjValue.$name."工资条信息";
				Mail::send('email.test', ['name' => $name ,'FSFHJ' => $v2['fsfhj'] , 'headerKeys'=>$headerKeys ,'header'=>$header, 'data'=>[$v2] ], function($message) use($v2, $subject) {
					$to = $v2['femail'];
					$message->to($to)->subject($subject);
				});
			}
			
			if (count(Mail::failures()) < 1) {
				showMsg('200','发送邮件成功！');
			} else {
				showMsg('400','发送邮件失败，请重试！');
			}
			
		}
	}
	
	
	
	///////////////////////////////////////////////////////////////////////////////
	///////////////////////////////////////////////////////////////////////////////
	/**
	 * 聚一科技 ^_^
	 * 导入   ---  通用
	 * @date: 2020年10月21日
	 * @author: lq
	 * @version:App V2.0
	 */
	public function zyInsertImport(request $request){
		set_time_limit(0);
		date_default_timezone_set('PRC');
		$file = $request->file('file');
		$FRepKey = $request->get('FRepKey');
		//dd($request->all() );
		//临时绝对路径
		$realPath = $file->getRealPath();
		//获取 后缀名getClientOriginalExtension
		$ext = $file->getClientOriginalExtension();
		//获取原始文件名getClientOriginalName
		$filename =$file->getClientOriginalName();
		$fileurl = Common::Http().$_SERVER['HTTP_HOST'].'/excel/'.$filename;
		$zhiyuan ='zhiyuan';
		if($FRepKey =='12001'){
			$zhiyuan ='zhiyuan';
		}elseif($FRepKey =='12002'){
			$zhiyuan ='jbfy';
		}elseif($FRepKey =='12005'){
			$zhiyuan ='kaoqin';
		}else{
			$zhiyuan =  columnBiao($FRepKey);
			
		}
		
		$name= $zhiyuan.'.xlsx';
		$url = $_SERVER['HTTP_HOST'].'/uploads/'.$filename;
		$res = Storage::disk('excels')->put($name,file_get_contents($realPath));
		
		//excels
		if($res){
			$filePath = public_path().'/excel/'.$name;
			$re =$this->_readExcel($filePath );
			if($re && count($re)>0){
				$re = array_values($re);
				//	$jichuData = $this->getjichuziliao($request);
				$FRepFldData = SqlServerUtil::execTosql($this->SqlServer,"select frepfld,frepcap,frepmeg,freptype from gz_RepListTitle where FRepKey ='{$FRepKey}' and FRepHide =0 and FUnControl =0  order by FRepIndex");
				$tempfname = [];
				$tempfield = [];
				$FRepFld =[];
				if(in_array($FRepKey,['12002','12005','12013','12225'  ]) && $FRepFldData){
					foreach ($FRepFldData as $k =>$v){
						//定制工资才有 工号
						if(strtolower($v['frepfld']) == 'fname'){
							$FRepFld[$k]= $v;
							$FRepFld[$k+1]['frepfld']= 'fnumber';
							$FRepFld[$k+1]['frepcap']= '工号';
							$FRepFld[$k+1]['frepmeg']= 0 ;
						} else{
							$FRepFld[] = $v;
						}
						
					}
				}else{
					$FRepFld = $FRepFldData;
				}
				
				foreach ($FRepFld as $k =>$v)
				{
					$tempfield[$k] = $v;
					$tempfield[$k]['frepfld'] = strtolower($v['frepfld']) ;
					$tempfname[] = $v['frepcap'];
				}
				
				$result =[];
				foreach ($re as $k =>$v)
				{
					foreach ($v as $k1 =>$v1)
					{
						if($tempfield[$k1]['frepmeg'] == 1){
							$result[$k][$tempfield[$k1]['frepfld']] =  $v1   ;
						}else{
							$result[$k][$tempfield[$k1]['frepfld']] = $v1;
						}
					}
				}

				//131    空字段串  转 整形--补0
				foreach ($result as $k =>&$v)
				{
					foreach ($tempfield as $k11 =>$v11)
					{
						if(empty($v[$v11['frepfld']])  &&  $v11['freptype'] == 131){
							$result[$k][$v11['frepfld']] = 0;
						}
					}
					
					//清单费
					if($FRepKey == 26005){
						$v['famount'] =  ($v['fprice']??0) * ($v['fqty']??0 ) ;
					}
					//气油费
					if($FRepKey == 26003){
						//气缸油单价*1.058
						$v['fcylinderoilprice'] =  ($v['fcylinderoilprice']??0) * 1.058 ;
						//油 气  合计
						$v['fAllamount'] = (($v['fuelqty']??0) * ($v['fprice']??0)) + (($v['fcylinderoil']??0) * $v['fcylinderoilprice']) + (($v['fmainoil']??0) * $v['fmainoilprice']) + (($v['fgasqty']??0) * ($v['fgasprice']??0 ) );
					}
					//专利合计
					if($FRepKey == 26004){
						$v['fPatentfeeAmount'] =  ($v['fexchangerate']??1) * ($v['fpatentfee']??0 ) ;
					}
					
					
					//ICER管件类采购成本
					if(in_array($FRepKey,['27007',  ]) ) {
						$limitStr = array(" ","\t","\n","\r");
						$v['fguanlutype'] =  str_replace($limitStr,"",$v['fguanlutype'] ,$count);  
						
// 						dd($count , $v['fguanlutype'] , strlen($v['fguanlutype']) ,trimall($v['fguanlutype']) );
// 						$v['fguanlutype'] = preg_replace("/\s|　/","",$v['fguanlutype']);
						//  =IF(D2="人民币",D4*C2,C2*E2*F2*1.13*1.06)
						if($v['fbibie']  == '人民币'){
							$v['fAmount'] =  $v['fqty'] * $v['fprice'] ; 
						}else{
							$v['fAmount'] =  round(($v['fhuilv']??1) * $v['fqty'] * $v['fprice'] * $this->cs_1_13 * $this->cs_1_06 ,2) ;
						}
						
					}
					
				}
				
// 				dd($result);
				//临时数据
				$tempData=[];
				$insert1 = true;
				$insert11 = true;
				
				$insert2 = false;
				$insert21 = false;
				$arr =[];
				$arr1=[];
				if (sqlsrv_begin_transaction($this->SqlServer) === false) {
					die(print_r(sqlsrv_errors(), true));
				}
				
				$db = columnBiao($FRepKey);
				$data = $result;
				//没有全部添加
				if(count($data)>400){
					$each=400;
					$total =count($data); //数据总数
					$step=ceil($total/$each); //insert执行总次数
					$j = 1;
					$s = $step;
					foreach ($data as $k=>&$v)
					{
						
						$arr2=[];$Tmp2 =[];
						if($j > $step) break;
						$arr2 = array_slice($data, ($step - $s) * $each, $each);     // 每次取500条
						$insert2 = SqlServerUtil::sqlsrvAllTosql($this->SqlServer,$db, $arr2);
						$j++;
						$s--;
					}
					
				}else{
					$insert2 = SqlServerUtil::sqlsrvAllTosql($this->SqlServer,$db, $data);
				}
			 
				if($insert2 ){
					sqlsrv_commit($this->SqlServer);
					showMsg(200,'上传文件成功 ');
				}else{
					sqlsrv_rollback($this->SqlServer);
					showMsg(400,'上传文件失败3');
				}
				
			}else{
				showMsg(400,'上传文件失败2');
			}
		}else{
			showMsg(400,'上传文件失败1');
		}
	}
	
	
	
	
	
	
	
	
}